﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class Customer2 : System.Web.UI.Page
{
    SqlConnection con;
    SqlCommand cmd;
    protected void Page_Load(object sender, EventArgs e)
    {
        
        loadcountry();
        FillCustomer();
        
       
        
    }
    void InitializeAOO()
    {
        con = new SqlConnection();
        con.ConnectionString = WebConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
        //con.Open();
        cmd = new SqlCommand();
        cmd.Connection = con;
    }
    void FillCustomer()
    {
        InitializeAOO();
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_getcustomer";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable("Customers");
        da.Fill(dt);
        
        gvCustomer.AutoGenerateSelectButton = true;
        gvCustomer.AllowPaging = true;
        gvCustomer.PageSize = 5;
        gvCustomer.DataSource = dt;
        gvCustomer.DataBind();
        
    }
    void InsertCustomer()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_insertCustomer";
            SqlParameter para = new SqlParameter("@companyName", SqlDbType.NVarChar);
            para.Size = 40;
            para.Value = txtCompanyname.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@contactname", SqlDbType.NVarChar);
            para.Size = 30;
            para.Value = txtContactName.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@contacttitle", SqlDbType.NVarChar);
            para.Size = 30;
            para.Value = txtContacttitle.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@address", SqlDbType.NVarChar);
            para.Size = 60;
            para.Value = txtAddress.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@city", SqlDbType.NVarChar);
            para.Size = 15;
            para.Value = txtCity.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@region", SqlDbType.NVarChar);
            if (txtRegion.Text.Trim().Length==0)
            {
                para.Size = 15;
                para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
            else
            {
                para.Size = 15;
                para.Value = txtRegion.Text;
                cmd.Parameters.Add(para);
            }
            

            para = new SqlParameter("@postalcode", SqlDbType.NVarChar);
            if (txtposCode.Text.Trim().Length==0)
            {
                para.Size = 10;
                para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
            else
            {
                para.Size = 10;
                para.Value = txtposCode.Text;
                cmd.Parameters.Add(para);
            }
           

            para = new SqlParameter("@country", SqlDbType.NVarChar);
            para.Size = 15;
            para.Value = DDLCountry.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@phone", SqlDbType.NVarChar);
            para.Size = 24;
            para.Value = txtPhone.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@fax", SqlDbType.NVarChar);
            if (txtFax.Text.Trim().Length==0)
            {
                para.Size = 24;
                para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
            else
            {
                para.Size = 24;
                para.Value = txtFax.Text;
                cmd.Parameters.Add(para);
               
            }

            cmd.ExecuteNonQuery();
            string script = "alert(\"Insert successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void UpdateCustomer()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_UpdateCustomer";
            SqlParameter para = new SqlParameter("@companyName", SqlDbType.NVarChar);
            para.Size = 40;
            para.Value = txtCompanyname.Text;
            cmd.Parameters.Add(para);
            para = new SqlParameter("@cusID", SqlDbType.Int);

            para.Value = txtID.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@contactname", SqlDbType.NVarChar);
            para.Size = 30;
            para.Value = txtContactName.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@contacttitle", SqlDbType.NVarChar);
            para.Size = 30;
            para.Value = txtContacttitle.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@address", SqlDbType.NVarChar);
            para.Size = 60;
            para.Value = txtAddress.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@city", SqlDbType.NVarChar);
            para.Size = 15;
            para.Value = txtCity.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@region", SqlDbType.NVarChar);
            if (txtRegion.Text.Trim().Length == 0)
            {
                para.Size = 15;
                para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
            else
            {
                para.Size = 15;
                para.Value = txtRegion.Text;
                cmd.Parameters.Add(para);
            }

            para = new SqlParameter("@postalcode", SqlDbType.NVarChar);
            if (txtposCode.Text.Trim().Length == 0)
            {
                para.Size = 10;
                para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
            else
            {
                para.Size = 10;
                para.Value = txtposCode.Text;
                cmd.Parameters.Add(para);
            }

            para = new SqlParameter("@country", SqlDbType.NVarChar);
            para.Size = 15;
            para.Value = DDLCountry.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@phone", SqlDbType.NVarChar);
            para.Size = 24;
            para.Value = txtPhone.Text;
            cmd.Parameters.Add(para);

            para = new SqlParameter("@fax", SqlDbType.NVarChar);
            if (txtFax.Text.Trim().Length == 0)
            {
                para.Size = 24;
                para.Value = DBNull.Value;
                cmd.Parameters.Add(para);
            }
            else
            {
                para.Size = 24;
                para.Value = txtFax.Text;
                cmd.Parameters.Add(para);
                
            }
            cmd.ExecuteNonQuery();
            string script = "alert(\"Update successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void deleteCustomer()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_deleteCustomer";
            SqlParameter para = new SqlParameter("@cusID", SqlDbType.Int);
            para.Value = txtID.Text;
            cmd.Parameters.Add(para);
            cmd.ExecuteNonQuery();

            string script = "alert(\"Delete successfull!\");";
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true);
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void loadcountry()
    {

        InitializeAOO();
        con.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_countryCus";
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            DDLCountry.Items.Add(dr[0].ToString());

        }
        dr.Close();
        con.Close();
    }
    void searchID()
    {

        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_SearchCustomer";
            SqlParameter para = new SqlParameter("@custID", SqlDbType.Int);
            para.Value = txtSearch.Text;
            cmd.Parameters.Add(para);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("seID");
            da.Fill(dt);
            gvCustomer.DataSource = dt;
            gvCustomer.DataBind();
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {

            con.Close();
        }
    }
    void searchname()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_SearchCustomerName";
            SqlParameter para = new SqlParameter("@contactname", SqlDbType.NVarChar);
            para.Size = 30;
            para.Value = txtSearch.Text.Trim();
            cmd.Parameters.Add(para);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("seName");
            da.Fill(dt);
            gvCustomer.DataSource = null;
            gvCustomer.DataBind();
            gvresult.DataSource = dt;
            gvresult.DataBind();
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void searchcountry()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_searchcountryCus";
            SqlParameter para = new SqlParameter("@country", SqlDbType.NVarChar);
            para.Size = 15;
            para.Value = ddlFilter.Text;
            cmd.Parameters.Add(para);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("seCountry");
            da.Fill(dt);

            gvCustomer.DataSource = null;
            gvCustomer.DataBind();
            gvresult.DataSource = dt;
            gvresult.DataBind();
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void searchTitle()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_searchCustomerTitle";
            SqlParameter para = new SqlParameter("@titleName", SqlDbType.NVarChar);
            para.Size = 30;
            para.Value = ddlFilter.Text;
            cmd.Parameters.Add(para);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable("setitle");
            da.Fill(dt);

            gvCustomer.DataSource = null;
            gvCustomer.DataBind();
            gvresult.DataSource = dt;
            gvresult.DataBind();
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void loadTitile()
    {
        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_gettitleCus";
            SqlDataReader dr = cmd.ExecuteReader();
            ddlFilter.Items.Clear();
            ddlFilter.DataBind();
            while (dr.Read())
            {
                ddlFilter.Items.Add(dr[0].ToString());

            }
            ddlFilter.DataBind();
            dr.Close();
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void loadcountryFil()
    {

        try
        {
            InitializeAOO();
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_countryCus";
            SqlDataReader dr = cmd.ExecuteReader();
            ddlFilter.Items.Clear();
            ddlFilter.DataBind();
            while (dr.Read())
            {
                ddlFilter.Items.Add(dr[0].ToString());


            }
            ddlFilter.DataBind();
            dr.Close();
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
    void clear()
    {
        btnDelete.Enabled = false;
        txtID.Text = "";
        txtCompanyname.Text = "";
        txtContactName.Text = "";
        txtContacttitle.Text = "";
        txtAddress.Text = "";
        txtCity.Text = "";
        txtRegion.Text = "";
        txtposCode.Text = "";
        DDLCountry.Items.Clear();
        txtPhone.Text = "";
        txtFax.Text = "";
    }
    protected void gvCustomer_SelectedIndexChanged(object sender, EventArgs e)
    {

        try
        {
            btnDelete.Enabled = true;
            GridViewRow r = gvCustomer.SelectedRow;

            txtID.Text = Server.HtmlDecode(r.Cells[1].Text);
            txtCompanyname.Text = Server.HtmlDecode(r.Cells[2].Text);
            txtContactName.Text = Server.HtmlDecode(r.Cells[3].Text);
            txtContacttitle.Text = Server.HtmlDecode(r.Cells[4].Text);
            txtAddress.Text = Server.HtmlDecode(r.Cells[5].Text);
            txtCity.Text = Server.HtmlDecode(r.Cells[6].Text);
            txtRegion.Text = Server.HtmlDecode(r.Cells[7].Text);
            txtposCode.Text = Server.HtmlDecode(r.Cells[8].Text);
            DDLCountry.Text = Server.HtmlDecode(r.Cells[9].Text);
            txtFax.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtPhone.Text = Server.HtmlDecode(r.Cells[11].Text);
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
       
    }
    protected void gvCustomer_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvCustomer.PageIndex = e.NewPageIndex;

        gvCustomer.DataBind();
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        InsertCustomer();
        gvresult.DataSource = null;
        gvresult.DataBind();
        FillCustomer();
    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        UpdateCustomer();
        gvresult.DataSource = null;
        gvresult.DataBind();
        FillCustomer();
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        deleteCustomer();
        gvresult.DataSource = null;
        gvresult.DataBind();
        FillCustomer();

    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        clear();
        gvresult.DataSource = null;
        gvresult.DataBind();
        loadcountry();
    }
    protected void btnsearch_Click(object sender, EventArgs e)
    {
        searchname();
    }
    protected void RBLFilter_SelectedIndexChanged(object sender, EventArgs e)
    {
        
        if (RBLFilter.SelectedItem.Text.Equals("Title"))
        {
            
            loadTitile();
            gvresult.DataSource = null;
            gvresult.DataBind();


        }
        if (RBLFilter.SelectedItem.Text.Equals("Country"))
        {
            loadcountryFil();
            gvresult.DataSource = null;
            gvresult.DataBind();

        }
    }
   
    protected void btnReload_Click(object sender, EventArgs e)
    {
        FillCustomer();
        gvresult.DataSource = null;
        gvresult.DataBind();
    }
    protected void ddlFilter_SelectedIndexChanged1(object sender, EventArgs e)
    {
        

        if (RBLFilter.SelectedItem.Text.Equals("Title"))
        {
            gvCustomer.DataSource = null;
            gvCustomer.DataBind();
            searchTitle();
            


        }
        if (RBLFilter.SelectedItem.Text.Equals("Country"))
        {
            gvCustomer.DataSource = null;
            gvCustomer.DataBind();
            searchcountry();


        }
    }
    protected void gvCustomer_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
    {
        
    }
    protected void gvresult_SelectedIndexChanged(object sender, EventArgs e)
    {
        try
        {
            btnDelete.Enabled = true;
            gvCustomer.DataSource = null;
            gvCustomer.DataBind();
            GridViewRow r = gvresult.SelectedRow;
            txtID.Text = Server.HtmlDecode(r.Cells[1].Text);
            txtCompanyname.Text = Server.HtmlDecode(r.Cells[2].Text);
            txtContactName.Text = Server.HtmlDecode(r.Cells[3].Text);
            txtContacttitle.Text = Server.HtmlDecode(r.Cells[4].Text);
            txtAddress.Text = Server.HtmlDecode(r.Cells[5].Text);
            txtCity.Text = Server.HtmlDecode(r.Cells[6].Text);
            txtRegion.Text = Server.HtmlDecode(r.Cells[7].Text);
            txtposCode.Text = Server.HtmlDecode(r.Cells[8].Text);
            DDLCountry.Text = Server.HtmlDecode(r.Cells[9].Text);
            txtFax.Text = Server.HtmlDecode(r.Cells[10].Text);
            txtPhone.Text = Server.HtmlDecode(r.Cells[11].Text);
        }
        catch (Exception ex)
        {
            string script = string.Format("alert(\"{0}\");", ex.Message);
            ScriptManager.RegisterStartupScript(this, GetType(),
                                  "ServerControlScript", script, true); ;

        }
        finally
        {
            con.Close();
        }
    }
}
